Re: [GENERAL] slow inserts and updates on large tables

At 5:02 +0200 on 17/2/99, Jim Mercer wrote:


> if anyone has any pointers as to why this is so slow, lemme know.

Have you checked the usual stuff:

1) Each insert and update creates its own transaction. That is, an
   insert is in fact:

   BEGIN TRANSACTION;
   INSERT...;
   COMMIT;

   So, to make things faster you should BEGIN TRANSACTION explicitly
   before all the inserts and COMMIT after them. Or separate into
   manageable bulks if you run into a memory problem.

2) Indexing tables mean faster SELECTs at the expense of slower INSERTs
   and UPDATEs. There is no magic. The reasoning is that normally you
   query the data a lot more than you change it.

   Thus, it is preferable, before doing bulk inserts, to drop the
   indices and recreate them afterwards. This is true when you are not
   expectind the database to be queried at the same time the inserts are
   made.

   As for updates, it's trickier, because you actually use the index
   for the WHERE part of the update. If speed is of an essence, I would
   probably try the following:

   SELECT * INTO TABLE temp_table FROM your_table WHERE update_condition;
   DELETE FROM your_table WHERE update_condition;
   DROP INDEX...;
   INSERT INTO your_table SELECT ... FROM temp_table; -- update within select
   CREATE INDEX...;

3) Back to the issue of INSERTS - copies are faster. If you can transform
   the data into tab-delimited format as required by COPY, you save a lot
   of time on parsing, planning etc.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma